﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_SaveSermon')
BEGIN
    PRINT 'Dropping Procedure proc_cm_SaveSermon'
    DROP  Procedure  proc_cm_SaveSermon
END
GO

PRINT 'Creating Procedure proc_cm_SaveSermon'
GO

CREATE PROCEDURE [dbo].[proc_cm_SaveSermon]
	@pSermonId uniqueidentifier = null
    ,@pVideoTypeId int
    ,@pTitleTxt nvarchar(100)
    ,@pSermonSeriesId uniqueidentifier = null
    ,@pPassageTxt nvarchar(max)
    ,@pPreachedDttm datetime = null
    ,@pYouTubeUrl varchar(255) = null
    ,@pPreacherNm nvarchar(100) = null
	,@pReferenceTxt nvarchar(255) = null
	,@pDownloadFilenameTxt varchar(100) = null
AS
BEGIN
	SET NOCOUNT ON
	
	IF @pPreachedDttm IS NULL
		SET @pPreachedDttm = GETDATE()
	
	IF @pSermonId IS NULL
		SET @pSermonId = NEWID()
		
	UPDATE 
		[dbo].[tbl_sermon]
	SET 
		[sermon_series_id] = @pSermonSeriesId
		,[title_txt] = @pTitleTxt
		,[video_type_id] = @pVideoTypeId
		,[passage_txt] = @pPassageTxt
		,[preached_dttm] = @pPreachedDttm
		,[update_dttm] = GETDATE()
		,[Youtube_url] = @pYouTubeUrl
		,[preacher_nm] = @pPreacherNm
		,[reference_txt] = @pReferenceTxt
		,[download_filename_txt] = @pDownloadFilenameTxt
	WHERE
		[sermon_id] = @pSermonId
			
	IF @@ROWCOUNT = 0
	BEGIN
		INSERT INTO [AllNationsDb].[dbo].[tbl_sermon]
		(
			[sermon_id]
			,[sermon_series_id]
			,[title_txt]
			,[video_type_id]
			,[passage_txt]
			,[preached_dttm]
			,[create_dttm]
			,[update_dttm]
			,[Youtube_url]
			,[preacher_nm]
			,[reference_txt]
			,[download_filename_txt]
		)
		VALUES
		(
			@pSermonId
			,@pSermonSeriesId
			,@pTitleTxt
			,@pVideoTypeId
			,@pPassageTxt
			,@pPreachedDttm
			,GETDATE()
			,GETDATE()
			,@pYouTubeUrl
			,@pPreacherNm
			,@pReferenceTxt
			,@pDownloadFilenameTxt
		)
	END
END
GO

GRANT EXEC ON dbo.proc_cm_SaveSermon TO PUBLIC
GO
